#!/usr/bin/env python
# coding: utf-8


import json
import logging
import MySQLdb
from MySQLdb.connections import Error, Connection
from config.config import configs
from utils.commontools import safe_dict_value
from utils.middlewares import *


class MysqlUtils(object):
    """
    Mysql 工具类
    """

    @staticmethod
    def connect(conf=configs['mysql']):
        try:
            conn = MySQLdb.connect(host=conf['host'],
                                   db=conf['database'],
                                   port=conf['port'],
                                   user=conf['user'],
                                   passwd=conf['password'],
                                   charset='utf8')
            return conn
        except Error as ex:
            logging.error('MySQL connections failed: {0}.'.format(ex))

    """
    工具类方法
    """

    @staticmethod
    @typeassert(conn=Connection, query=str, args=tuple)
    def common_query(conn=None, query='', args=()):
        """
        Common Query
        :param conn:
        :param query:
        :param args:
        :return: cursor
        """

        if not conn or not query:
            raise TypeError
        try:
            cursor = conn.cursor()
            if args:
                cursor.execute(query, args)
            else:
                cursor.execute(query)
            return cursor
        except Exception as error:
            logging.error('Common query error:{0}'.format(error))

    @staticmethod
    @typeassert(conn=Connection, table=str)
    def select_all(conn=None, table=''):
        """
        Select all table data to array of dict_item
        :param conn:
        :param table:
        :return:
        """

        if not conn or not table:
            raise TypeError

        cursor = MysqlUtils.common_query(conn=conn, query="select * from {0};".format(table))
        rows = cursor.fetchall()
        dict_rows = []
        for row in rows:
            dict_item = MysqlUtils.sql_row_to_dict(cursor=cursor, row=row)
            dict_rows.append(dict_item)
        return dict_rows

    @staticmethod
    @typeassert(conn=Connection, table=str, column=str)
    def column_select(conn=None, table='', column='', value=None):
        """
        condition query
        :param conn:
        :param table:
        :param column:
        :param value:
        :return:
        """
        if not conn or not table or not column or not value:
            raise RuntimeError

        try:
            query = 'SELECT * FROM {0} WHERE {1}=%s'.format(table, column)
            args = (value,)
            cursor = MysqlUtils.common_query(conn, query, args)
            return cursor
        except Error as error:
            logging.error('Column_select find all error: {0}.'.format(error))

    @staticmethod
    @typeassert(table=str, column=str)
    def column_insert(self, table, column, value):
        """
        insert query (single)
        :param self:
        :param table:
        :param column:
        :param value:
        :return:
        """
        if not value:
            raise TypeError

        conn = self.conn
        cursor = self.column_select(conn=conn, table=table, column=column, value=value)
        if cursor.fetchone():
            logging.warning('Already exists on insert id', cursor.lastrowid)
        else:
            self.columns_insert(conn=conn, table=table, columns=(column,), values=(value,))

    @staticmethod
    @typeassert(conn=Connection, table=str, columns=tuple, values=tuple)
    def columns_insert(conn=None, table='', columns=(), values=()):
        """
        insert query (single)
        :param conn:
        :param table:
        :param columns:
        :param values:
        :return:
        """

        if not conn or not table or not columns or not values:
            raise TypeError

        try:
            columns_sql = MysqlUtils.tuple_to_sql(columns)
            value_sql = MysqlUtils.multiple_str(item='%s', multiple=len(columns))
            query = 'INSERT INTO {0} {1} VALUES({2})'.format(table, columns_sql, value_sql)
            cursor = MysqlUtils.common_query(conn, query, values)
            conn.commit()
            return cursor
        except Exception as error:
            logging.warning('Columns_insert error: {0}.'.format(error))

    @staticmethod
    @typeassert(conn=Connection, table=str, columns=tuple, values=tuple)
    def columns_update(conn=None, table='', columns=(), values=(),
                       select_column='', select_value=None):
        """
        update query
        :param conn:
        :param table:
        :param columns:
        :param values:
        :param select_column:
        :param select_value:
        :return:
        """

        if not conn or not table or not columns or not values:
            raise TypeError

        try:
            columns_sql = MysqlUtils.tuple_to_update_sql(columns)
            query = 'UPDATE {0} SET {1} WHERE {2}=%s;'.format(table, columns_sql, select_column)
            args = tuple(list(values) + [select_value])
            cursor = MysqlUtils.common_query(conn, query, args)
            conn.commit()
            return cursor
        except Exception as error:
            logging.error('Columns_update error: {0}.'.format(error))

    @staticmethod
    @typeassert(conn=Connection, query=str, params=list)
    def batch_insert(conn=None, query='', params=list()):
        """
        execute batch insert sql query
        :param conn:
        :param query:
        :param params:
        :return:
        """
        if not conn or not query:
            raise TypeError
        try:
            cursor = conn.cursor()
            cursor.executemany(query, params)
            conn.commit()
            return cursor
        except Exception as error:
            open('batch_insert_error.txt', 'a').write('{0} {1}\n'
                                                      .format(query, json.dumps(params, ensure_ascii=False)))
            logging.error('execute batch insert query error:{0}'.format(error))

    @staticmethod
    @typeassert(conn=Connection, table=str, column=str)
    def common_delete(conn=None, table='', column='', value=None):
        """
        delete
        :param conn:
        :param table:
        :param column:
        :param value:
        :return:
        """
        if not conn or not table or not column or not value:
            raise RuntimeError
        try:
            query = 'DELETE FROM '+table+' WHERE '+column+'=%s;'
            args = (value,)
            cursor = MysqlUtils.common_query(conn, query, args)
            conn.commit()
            return cursor
        except Error as error:
            logging.error('delete records error: {0}'.format(error))

    """
    sql拼装
    """

    @staticmethod
    @typeassert(obj=tuple)
    def tuple_to_sql(obj=()):
        """
         Tuple to sql like ('a','b','c')=>"(a,b,c)"
        :param obj:
        :return:
        """
        return str(obj).replace('\'', '')

    @staticmethod
    @typeassert(obj=tuple)
    def tuple_to_plaint_sql(obj=()):
        """
         Tuple to sql like ('a','b','c')=>"a,b,c"
        :param obj:
        :return:
        """
        return str(obj).replace('\'', '').replace('(', '').replace(')', '')

    @staticmethod
    def multiple_str(item='', multiple=0):
        """
        Create str like "%,%@,%@"
        :param item:
        :param multiple:
        :return: item,item,item
        """
        m = []
        m.extend([item] * multiple)
        return ','.join(m)

    @staticmethod
    def tuple_to_update_sql(obj=()):
        """
        Tuple to update sql like ('a','b','c')=>"a=%,b=%,c=%"
        :return:
        """

        new_sql = list()
        for column in obj:
            if isinstance(column, unicode):
                column = str(column)
            new_sql.append(column + '=%s')
        new_sql = tuple(new_sql)
        result_sql = str(new_sql).replace('\'', '').replace('(', '').replace(')', '')
        if result_sql.endswith(','):
            result_sql = result_sql[:-1]
        return result_sql

    @staticmethod
    def sql_row_to_dict(cursor=None, row=None):
        """
        Sql result row to dict
        :param cursor:
        :param row:
        :return:
        """

        try:
            result = dict()
            columns = [desc[0] for desc in cursor.description]
            for k, v in zip(columns, row):
                result[k] = v
            return result
        except Error as error:
            logging.error('sql_row_to_dict error: {0}.'.format(error))

    @staticmethod
    @typeassert(items=list, column=str)
    def filter_column_items(items=list(), column='', value=''):
        """
        过滤出items中 item[column]=value的所有项
        :param items:
        :param column:
        :param value:
        :return:
        """

        if not items or len(items) == 0:
            return []
        if not column or len(column) == 0:
            return []

        result = list()
        for item in items:
            matching_value = safe_dict_value(item=item, key=column, default='')
            if matching_value == value:
                result.append(item)
        return result

    @staticmethod
    @typeassert(items=list, source_key=str, target_key=str)
    def list_match_value_with_items(value=None, items=list(), source_key='', target_key='', like=False):
        """
        match_value_with_items 兼容value为'value,value...'的场景
        :param value:
        :param items:
        :param source_key:
        :param target_key:
        :param like:
        :return:
        """

        if not items or not source_key or not target_key:
            raise TypeError
        if not value or len(value) == 0:
            return ''
        if len(value.split(',')) <= 1:
            return MysqlUtils.match_value_with_items(value=value, items=items,
                                                     source_key=source_key, target_key=target_key, like=like)
        result = ''
        for single_value in value.split(','):
            result = MysqlUtils.match_value_with_items(value=single_value.strip(), items=items,
                                                       source_key=source_key, target_key=target_key, like=like)
            if len(result) > 0:
                break
        return result

    @staticmethod
    @typeassert(items=list, source_key=str, target_key=str)
    def match_value_with_items(value=None, items=list(), source_key='', target_key='', like=False):
        """
        Math value with item[source_key] return item[target_key] or ''
        :param value:
        :param items:
        :param source_key:
        :param target_key:
        :param like true for like match or equal match
        :return:
        """
        if not items or not source_key or not target_key:
            raise TypeError
        if not value or len(value) == 0:
            return ''

        _has_match_value = False
        item = ''
        for item in items:
            matching_value = safe_dict_value(item=item, key=source_key, default='')
            if len(matching_value) == 0:
                continue
            if like:
                _has_match_value = matching_value.find(value) >= 0
            else:
                _has_match_value = (matching_value == value)
            if _has_match_value:
                break

        if _has_match_value:
            return safe_dict_value(item=item, key=target_key, default='')
        return ''
